package com.mybatispluslambda.poi;

import lombok.Builder;
import lombok.Data;
import lombok.ToString;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.util.StringUtils;

import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.LinkedList;
import java.util.List;
import java.util.stream.Collectors;

@SuppressWarnings("all")
public class PoiReader {

    public static void main(String[] args) throws Exception {
        readExcel("C:\\Users\\ruby6\\Desktop\\生物摸底考试成绩.xlsx");
    }

    public static void readExcel(String excelName) throws IOException {
        //将文件读入
        InputStream in = new FileInputStream(new File(excelName));
        //创建工作簿
        List<String> heads = new ArrayList<>();
        List<Student> clazz3 = new LinkedList<>();
        List<Student> clazz4 = new LinkedList<>();
        List<Student> clazz5 = new LinkedList<>();
        List<Student> clazz6 = new LinkedList<>();
        List<Student> clazz7 = new LinkedList<>();
        List<Student> clazz8 = new LinkedList<>();
        List<Student> clazz9 = new LinkedList<>();
        try (XSSFWorkbook wb = new XSSFWorkbook(in)) {
            //读取第一个sheet
            XSSFSheet sheet = wb.getSheetAt(1);
            for (int i = 0; i < 250; i++) {
                XSSFRow row = sheet.getRow(i);
                if (row == null) continue;
                if (i == 0) {
                    for (int z = 0; z < 13; z++) {
                        XSSFCell cell = row.getCell(z);
                        if (cell == null) continue;
                        heads.add(cell.getStringCellValue());
                    }
                }
                XSSFCell numberCell = row.getCell(0);
                String s = numberCell.toString();
                if (s.startsWith("33")) {
                    clazz3.add(setStudent(row, s));
                }
                if (s.startsWith("34")) {
                    clazz4.add(setStudent(row, s));
                }
                if (s.startsWith("35")) {
                    clazz5.add(setStudent(row, s));
                }
                if (s.startsWith("36")) {
                    clazz6.add(setStudent(row, s));
                }
                if (s.startsWith("37")) {
                    clazz7.add(setStudent(row, s));
                }
                if (s.startsWith("38")) {
                    clazz8.add(setStudent(row, s));
                }
                if (s.startsWith("39")) {
                    clazz9.add(setStudent(row, s));
                }
            }

        }


        //创建工作簿
        XSSFWorkbook xssFWriter = new XSSFWorkbook();
        createSheet(heads, clazz3, xssFWriter, "3班成绩");
        createSheet(heads, clazz4, xssFWriter, "4班成绩");
        createSheet(heads, clazz5, xssFWriter, "5班成绩");
        createSheet(heads, clazz6, xssFWriter, "6班成绩");
        createSheet(heads, clazz7, xssFWriter, "7班成绩");
        createSheet(heads, clazz8, xssFWriter, "8班成绩");
        createSheet(heads, clazz9, xssFWriter, "9班成绩");
        FileOutputStream out = new FileOutputStream("C:\\Users\\ruby6\\Desktop\\仙女请验收哦.xlsx");
        xssFWriter.write(out);
    }

    private static Student setStudent(XSSFRow row, String number) {
        if (number.length() == 6) {
            number = number.substring(0, number.length() - 2);
        }
        Student student = Student.builder().number(Integer.valueOf(number)).name(row.getCell(1).toString())
                .shengwu(row.getCell(10).toString()).build();
        System.out.println(student);
        return student;
    }

    private static void createSheet(List<String> heads, List<Student> students, XSSFWorkbook xssFWriter, String sheetName) {
        //创建一个sheet
        students = students.stream().sorted(Comparator.comparing(Student::getNumber)).collect(Collectors.toList());
        XSSFSheet sheets = xssFWriter.createSheet(sheetName);
        // 创建单元格样式
        XSSFCellStyle style = xssFWriter.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); //文字水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//文字垂直居中
        style.setBorderBottom(BorderStyle.THIN); //底边框加黑
        style.setBorderLeft(BorderStyle.THIN);  //左边框加黑
        style.setBorderRight(BorderStyle.THIN); // 有边框加黑
        style.setBorderTop(BorderStyle.THIN); //上边框加黑
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置前景填充样式
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());//前景填充色

        heads.remove("顺序");
        heads.remove("试场号");
        heads.remove("座位号");
        heads.remove("序号");

        for (int i = 0; i < students.size() + 3; i++) {
            Row row = sheets.createRow(i);
            for (int j = 0; j < heads.size(); j++) {
                row.createCell(j).setCellStyle(style);
            }
        }

        sheets.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));

        XSSFRow row = sheets.getRow(0);
        row.getCell(0).setCellValue("仙女真好看之" + sheetName);
        XSSFRow row1 = sheets.getRow(1);

        for (int i = 0; i < heads.size(); i++) {
            XSSFCell cell = row1.getCell(i);
            cell.setCellValue(heads.get(i));
            cell.setCellStyle(style);
        }

        //从第三行开始操作
        for (int i = 0; i < students.size(); i++) {
            XSSFRow row2 = sheets.getRow(i + 2);
            XSSFCell cell0 = row2.getCell(0);
            if (cell0 != null) {
                cell0.setCellValue(students.get(i).getNumber());
            }
            XSSFCell cell1 = row2.getCell(1);
            if (cell1 != null) {
                cell1.setCellValue(students.get(i).getName());
            }
            XSSFCell cell7 = row2.getCell(7);
            if (cell7 != null) {
                cell7.setCellValue(students.get(i).getShengwu());
            }
        }

        XSSFRow rowAverage = sheets.getRow(students.size() + 2);
        XSSFCell cell0 = rowAverage.getCell(0);
        cell0.setCellStyle(style);
        cell0.setCellValue("平均分");

        BigDecimal firstNumber = new BigDecimal("0");
        for (Student student : students) {
            String shengwu = student.getShengwu();
            if (StringUtils.isEmpty(shengwu)) continue;
            firstNumber = firstNumber.add(new BigDecimal(shengwu));
        }
        if (CollectionUtils.isEmpty(students)) return;
        BigDecimal divide = firstNumber.divide(new BigDecimal(students.size()), 0, RoundingMode.HALF_DOWN);
        rowAverage.getCell(7).setCellValue(String.format("%s.0", divide.toString()));
    }


    @Data
    @Builder
    @ToString
    public static class Student {
        private Integer number;
        private String name;
        private String site;
        private String address;
        private String yuwen;
        private String shuxue;
        private String yingyu;
        private String wuli;
        private String huaxue;
        private String shengwu;
        private String zhengzhi;
        private String dili;
    }
}
